﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Xml;
using System.Xml.Xsl;
using System.Collections;

namespace V.Utility
{
    /// <summary>
    /// 文件导出支持类
    /// <para>*  支持文件类型：Excel和CSV</para>
    /// </summary>
    /// 创建人：
    /// 创建日期：
    public class ExportHelper
    {
        #region Members
        /// <summary>
        /// 编码格式
        /// </summary>
        private const string EncodeStr = "GB2312";
        #endregion

        #region Export Excel Methods
        /// <summary>
        /// 导出Excel表格
        /// <para>*  支持GridView导出Excel</para>
        /// </summary>
        /// <param name="gdv">GridView</param>
        /// <param name="fileName">文件名,可为空。为空时默认文件名：导出数据.xls</param>
        /// <param name="title">标题,无标题设为空</param>
        public static void ExportExcel(GridView gdv, string fileName, string title)
        {
            DataTable dt = new DataTable();
            for (int i = 0; i < gdv.Columns.Count; i++)
            {
                if (gdv.Columns[i].HeaderText == "" || gdv.Columns[i].Visible == false)
                    continue;
                dt.Columns.Add(gdv.Columns[i].HeaderText);
            }

            for (int i = 0; i < gdv.Rows.Count; i++)
            {
                DataRow dr = dt.NewRow();
                for (int j = 0; j < gdv.Columns.Count; j++)
                {
                    if (gdv.Columns[j].HeaderText == "" || gdv.Columns[j].Visible == false)
                        continue;
                    dr[gdv.Columns[j].HeaderText] = gdv.Rows[i].Cells[j].Text;
                }
                dt.Rows.Add(dr);
            }
            ExportExcel(dt, fileName, title);
        }


        /// <summary>
        /// 导出Excel表格
        /// <para>*  注意事项：DataTable的列头将转为Excel表格的列头,</para>
        /// </summary>
        /// <param name="dt">数据集</param>
        /// <param name="fileName">文件名,可为空。为空时默认文件名：导出数据.xls</param>
        /// <param name="title">标题,无标题设为空</param>
        public static void ExportExcel(DataTable dt, string fileName, string title)
        {
            ExportExcel(dt, fileName, null, null, title);
        }


        /// <summary>
        /// 导出Excel表格
        /// </summary>
        /// <param name="dt">数据</param>
        /// <param name="fileName">文件名,可为空。为空时默认文件名：导出数据.xls</param>
        /// <param name="columnNames">列名，数量必须与列的数目一致，否则直接加载DataTable的列名。</param>
        /// <param name="title">标题,无标题设为空</param>
        public static void ExportExcel(DataTable dt, string fileName, string[] columnNames, string title)
        {
            ExportExcel(dt, fileName, null, columnNames, title);
        }

        /// <summary>
        /// 导出Excel表格
        /// </summary>
        /// <param name="dt">数据</param>
        /// <param name="fileName">文件名,可为空。为空时默认文件名：导出数据.xls</param>
        /// <param name="heads">表头部分信息，表头的行数和字符串组的length一样。</param>
        /// <param name="columnNames">列名，数量必须与列的数目一致，否则直接加载DataTable的列名。</param>
        /// <param name="title">标题,无标题设为空</param>
        public static void ExportExcel(DataTable dt, string fileName, string[] heads, string[] columnNames, string title)
        {
            //头部
            StringBuilder strExcel = new StringBuilder();
            strExcel.Append("<meta http-equiv=\"Content-Type\" content=\"text/html\" charset=\"" + EncodeStr +
                            "\"><style>td{mso-number-format:\"\\@\";}</style><table  border=\"1\"><tr>");
            //添加标题
            if (!string.IsNullOrEmpty(title))
            {
                strExcel.Append("<th colspan='" + (dt.Columns.Count) + "'>");
                strExcel.Append(title);
                strExcel.Append("</th></tr><tr>");
            }
            //添加报表头部
            if (heads != null && heads.Length > 0)
            {
                foreach (string header in heads)
                {
                    strExcel.Append("<td colspan='" + (dt.Columns.Count) + "'>");
                    strExcel.Append(header);
                    strExcel.Append("</td></tr><tr>");
                }
            }

            //添加列头
            if (columnNames != null && columnNames.Length == dt.Columns.Count)
            {
                foreach (string columnName in columnNames)
                {
                    strExcel.Append("<th>");
                    strExcel.Append(columnName);
                    strExcel.Append("</th>");
                }
            }
            else
            {
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    strExcel.Append("<th>");
                    strExcel.Append(dt.Columns[i].ColumnName);
                    strExcel.Append("</th>");
                }
            }
            strExcel.Append("</tr>");

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                strExcel.Append("<tr>");
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    strExcel.Append("<td>");
                    strExcel.Append(dt.Rows[i][j]);
                    strExcel.Append("</td>");
                }
                strExcel.Append("</tr>");
            }
            strExcel.Append("</table>");

            if (fileName == "")
                fileName = "导出数据";

            byte[] b = Encoding.Default.GetBytes(strExcel.ToString());
            MemoryStream ms = new MemoryStream();
            ms.Write(b, 0, b.Length);
            HttpContext.Current.Response.Clear();
            HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename="
                + HttpContext.Current.Server.UrlEncode(fileName) + ".xls");
            HttpContext.Current.Response.ContentEncoding = Encoding.GetEncoding(EncodeStr);
            HttpContext.Current.Response.ContentType = "application/ms-excel";
            HttpContext.Current.Response.AddHeader("Content-Length", ms.Length.ToString());
            HttpContext.Current.Response.BinaryWrite(ms.ToArray());
            ms.Close();
            HttpContext.Current.Response.End();
        }

        /// <summary>
        /// 导出Excel 支持多个sheet
        /// </summary>
        /// <param name="ds">数据源</param>
        /// <param name="fileName">文件名</param>
        /// <param name="sheetNames">sheet名称数组，数组个数和数据表的个数保持一致</param>
        public static void ExportExcel(DataSet ds, string fileName, string[] sheetNames)
        {
            HttpContext.Current.Response.ClearContent();
            HttpContext.Current.Response.BufferOutput = true;
            HttpContext.Current.Response.Charset = "utf-8";
            HttpContext.Current.Response.ContentType = "application/ms-excel";
            HttpContext.Current.Response.AddHeader("Content-Transfer-Encoding", "binary");
            HttpContext.Current.Response.ContentEncoding = Encoding.UTF8;

            HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
            HttpContext.Current.Response.Write("<?xml version='1.0'?><?mso-application progid='Excel.Sheet'?>");
            HttpContext.Current.Response.Write(@"<Workbook xmlns='urn:schemas-microsoft-com:office:spreadsheet'
              xmlns:o='urn:schemas-microsoft-com:office:office' xmlns:x='urn:schemas-microsoft-com:office:excel'
              xmlns:ss='urn:schemas-microsoft-com:office:spreadsheet' xmlns:html='http://www.w3.org/TR/REC-html40'>");

            HttpContext.Current.Response.Write(@"<Styles><Style ss:ID='Default' ss:Name='Normal'><Alignment ss:Vertical='Center'/>
                <Borders/><Font ss:FontName='宋体' x:CharSet='134' ss:Size='12'/><Interior/><NumberFormat/><Protection/></Style>");
            //定义标题样式    
            HttpContext.Current.Response.Write(@"<Style ss:ID='Header'><Borders><Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/>
               <Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/>
               <Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/>
               <Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/></Borders>
               <Font ss:FontName='宋体' x:CharSet='134' ss:Size='12' ss:Bold='1'/></Style>");

            //定义边框
            HttpContext.Current.Response.Write(@"<Style ss:ID='border'><NumberFormat ss:Format='@'/><Borders>
              <Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/>
              <Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/>
              <Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/>
              <Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/></Borders></Style>");

            HttpContext.Current.Response.Write("</Styles>");

            for (int i = 0; i < ds.Tables.Count; i++)
            {
                if (sheetNames == null || sheetNames.Length != ds.Tables.Count)
                    HttpContext.Current.Response.Write("<Worksheet ss:Name='" + ds.Tables[i].TableName + "'>");
                else
                    HttpContext.Current.Response.Write("<Worksheet ss:Name='" + sheetNames[i] + "'>");
                HttpContext.Current.Response.Write("<Table x:FullColumns='1' x:FullRows='1'>");

                //输出标题
                HttpContext.Current.Response.Write("\r\n<Row ss:AutoFitHeight='1'>");
                for (int j = 0; j < ds.Tables[i].Columns.Count; j++)
                {
                    HttpContext.Current.Response.Write("<Cell ss:StyleID='Header'><Data ss:Type='String'>" + ds.Tables[i].Columns[j].ColumnName + "</Data></Cell>");
                }
                HttpContext.Current.Response.Write("\r\n</Row>");

                foreach (DataRow dr in ds.Tables[i].Rows)
                {
                    HttpContext.Current.Response.Write("<Row>");
                    foreach (DataColumn dc in ds.Tables[i].Columns)
                    {
                        HttpContext.Current.Response.Write("<Cell ss:StyleID='border'><Data ss:Type='String'>" + dr[dc] + "</Data></Cell>");
                    }
                    HttpContext.Current.Response.Write("</Row>");
                }
                HttpContext.Current.Response.Write("</Table>");
                HttpContext.Current.Response.Write("</Worksheet>");
            }
            HttpContext.Current.Response.Write("</Workbook>");
            HttpContext.Current.Response.End();
        }

        #endregion

        #region Export CSV Methods

        /// <summary>
        /// 动态生成XSL，并写入XML流
        /// </summary>
        /// <param name="writer">XML流</param>
        /// <param name="headers">表头数组</param>
        /// <param name="fields">字段数组</param>
        /// <param name="title">标题,无标题设为空</param>
        private static void CreateStylesheet(XmlTextWriter writer, string[] headers, string[] fields, string title)
        {
            string ns = "http://www.w3.org/1999/XSL/Transform";
            writer.Formatting = Formatting.Indented;
            writer.WriteStartDocument();
            writer.WriteStartElement("xsl", "stylesheet", ns);
            writer.WriteAttributeString("version", "1.0");
            writer.WriteStartElement("xsl:output");
            writer.WriteAttributeString("method", "text");
            writer.WriteAttributeString("version", "4.0");
            writer.WriteEndElement();

            // xsl-template
            writer.WriteStartElement("xsl:template");
            writer.WriteAttributeString("match", "/");

            // xsl:value-of for title
            if (!string.IsNullOrEmpty(title))
            {
                writer.WriteString("\"");
                writer.WriteStartElement("xsl:value-of");
                writer.WriteAttributeString("select", "'" + title + "'");
                writer.WriteEndElement(); // xsl:value-of
                writer.WriteString("\",\r\n");
            }

            // xsl:value-of for headers
            if (headers != null && headers.Length > 0)
            {
                for (int i = 0; i < headers.Length; i++)
                {
                    writer.WriteString("\"");
                    writer.WriteStartElement("xsl:value-of");
                    writer.WriteAttributeString("select", "'" + headers[i] + "'");
                    writer.WriteEndElement(); // xsl:value-of
                    writer.WriteString("\"");
                    if (i != fields.Length - 1) writer.WriteString(",");
                }
            }

            // xsl:for-each
            writer.WriteStartElement("xsl:for-each");
            writer.WriteAttributeString("select", "Export/Values");
            writer.WriteString("\r\n");

            // xsl:value-of for data fields
            if (fields != null && fields.Length > 0)
            {
                for (int i = 0; i < fields.Length; i++)
                {
                    writer.WriteString("\"");
                    writer.WriteStartElement("xsl:value-of");
                    writer.WriteAttributeString("select", fields[i]);
                    writer.WriteEndElement(); // xsl:value-of
                    writer.WriteString("\"");
                    if (i != fields.Length - 1) writer.WriteString(",");
                }
            }

            writer.WriteEndElement(); // xsl:for-each
            writer.WriteEndElement(); // xsl-template
            writer.WriteEndElement(); // xsl:stylesheet
        }

        /// <summary>
        /// 导出CSV
        /// </summary>
        /// <param name="dt">数据源</param>
        /// <param name="fileName">输出文件名</param>
        /// <param name="title">标题,无标题设为空</param>
        public static void ExportCSV(DataTable dt, string fileName, string title)
        {
            DataSet dsExport = new DataSet("Export");
            DataTable dtExport = dt.Copy();

            dtExport.TableName = "Values";
            dsExport.Tables.Add(dtExport);

            string[] headers = new string[dtExport.Columns.Count];
            string[] fields = new string[dtExport.Columns.Count];

            for (int i = 0; i < dtExport.Columns.Count; i++)
            {
                headers[i] = dtExport.Columns[i].ColumnName;
                fields[i] = ReplaceSpecialChars(dtExport.Columns[i].ColumnName);
            }

            ExportCSV(dsExport, headers, fields, fileName, title);
        }

        /// <summary>
        /// 导出CSV
        /// </summary>
        /// <param name="dt">数据源</param>
        /// <param name="columnIndexList">导出的列索引数组</param>
        /// <param name="fileName">输出文件名</param>
        /// <param name="title">标题,无标题设为空</param>
        public static void ExportCSV(DataTable dt, int[] columnIndexList, string fileName, string title)
        {
            DataSet dsExport = new DataSet("Export");
            DataTable dtExport = dt.Copy();

            dtExport.TableName = "Values";
            dsExport.Tables.Add(dtExport);

            string[] headers = new string[columnIndexList.Length];
            string[] fields = new string[columnIndexList.Length];

            for (int i = 0; i < columnIndexList.Length; i++)
            {
                headers[i] = dtExport.Columns[columnIndexList[i]].ColumnName;
                fields[i] = ReplaceSpecialChars(dtExport.Columns[columnIndexList[i]].ColumnName);
            }

            ExportCSV(dsExport, headers, fields, fileName, title);
        }

        /// <summary>
        /// 导出CSV
        /// </summary>
        /// <param name="dt">数据源</param>
        /// <param name="columnNameList">导出的列的列名数组</param>
        /// <param name="fileName">输出文件名</param>
        /// <param name="title">标题,无标题设为空</param>
        public static void ExportCSV(DataTable dt, string[] columnNameList, string fileName, string title)
        {
            List<int> columnIndexList = new List<int>();
            DataColumnCollection dcc = dt.Columns;

            if (columnNameList != null && columnNameList.Length == dt.Columns.Count)
            {
                columnIndexList.AddRange(columnNameList.Select(s => GetColumnIndexByColumnName(dcc, s)));
            }
            else
            {
                columnIndexList.AddRange(from DataColumn c in dt.Columns select GetColumnIndexByColumnName(dcc, c.ColumnName));
            }

            ExportCSV(dt, columnIndexList.ToArray(), fileName, title);
        }

        /// <summary>
        /// 导出CSV
        /// </summary>
        /// <param name="dt">数据源</param>
        /// <param name="columnIndexList">导出的列索引数组</param>
        /// <param name="heads">导出的列标题数组</param>
        /// <param name="fileName">输出文件名</param>
        /// <param name="title">标题,无标题设为空</param>
        public static void ExportCSV(DataTable dt, int[] columnIndexList, string[] heads, string fileName, string title)
        {
            DataSet dsExport = new DataSet("Export");
            DataTable dtExport = dt.Copy();

            dtExport.TableName = "Values";
            dsExport.Tables.Add(dtExport);

            string[] fields = new string[columnIndexList.Length];

            for (int i = 0; i < columnIndexList.Length; i++)
            {
                fields[i] = ReplaceSpecialChars(dtExport.Columns[columnIndexList[i]].ColumnName);
            }

            ExportCSV(dsExport, heads, fields, fileName, title);
        }

        /// <summary>
        /// 导出CSV
        /// </summary>
        /// <param name="dt">数据源</param>
        /// <param name="columnNameList">导出的列的列名数组</param>
        /// <param name="heads">导出的列标题数组</param>
        /// <param name="fileName">输出文件名</param>
        /// <param name="title">标题,无标题设为空</param>
        public static void ExportCSV(DataTable dt, string[] columnNameList, string[] heads, string fileName, string title)
        {
            List<int> columnIndexList = new List<int>();
            DataColumnCollection dcc = dt.Columns;

            if (columnNameList != null && columnNameList.Length == dt.Columns.Count)
            {
                columnIndexList.AddRange(columnNameList.Select(s => GetColumnIndexByColumnName(dcc, s)));
            }
            else
            {
                columnIndexList.AddRange(from DataColumn c in dt.Columns select GetColumnIndexByColumnName(dcc, c.ColumnName));
            }

            ExportCSV(dt, columnIndexList.ToArray(), heads, fileName, title);
        }

        /// <summary>
        /// 导出CSV
        /// </summary>
        /// <param name="ds">数据源</param>
        /// <param name="heads">导出的表头数组</param>
        /// <param name="fields">导出的字段数组</param>
        /// <param name="fileName">输出文件名</param>
        /// <param name="title">标题,无标题设为空</param>
        private static void ExportCSV(DataSet ds, string[] heads, string[] fields, string fileName, string title)
        {
            HttpContext.Current.Response.Clear();
            HttpContext.Current.Response.Buffer = true;
            HttpContext.Current.Response.ContentType = String.Format("text/{0}", ExportFormat.CSV.ToString().ToLower());
            HttpContext.Current.Response.Charset = EncodeStr;
            HttpContext.Current.Response.AddHeader("content-disposition", String.Format("attachment;filename={0}.{1}", HttpContext.Current.Server.UrlEncode(fileName), ExportFormat.CSV.ToString().ToLower()));
            HttpContext.Current.Response.ContentEncoding = Encoding.GetEncoding(EncodeStr);

            MemoryStream stream = new MemoryStream();
            XmlTextWriter writer = new XmlTextWriter(stream, Encoding.GetEncoding(EncodeStr));

            CreateStylesheet(writer, heads, fields, title);
            writer.Flush();
            stream.Seek(0, SeekOrigin.Begin);

            XmlDataDocument xmlDoc = new XmlDataDocument(ds);
            XslCompiledTransform xslTran = new XslCompiledTransform();
            xslTran.Load(new XmlTextReader(stream));

            var sw = new StringWriter();
            xslTran.Transform(xmlDoc, null, sw);

            HttpContext.Current.Response.Write(sw.ToString());
            sw.Close();
            writer.Close();
            stream.Close();
            HttpContext.Current.Response.End();
        }

        /// <summary>
        ///  替换特殊字符
        /// </summary>
        /// <param name="input">字符串</param>
        /// <returns></returns>
        public static string ReplaceSpecialChars(string input)
        {
            // space 	-> 	_x0020_
            // %		-> 	_x0025_
            // #		->	_x0023_
            // &		->	_x0026_
            // /		->	_x002F_

            input = input.Replace(" ", "_x0020_")
                .Replace("%", "_x0025_")
                .Replace("#", "_x0023_")
                .Replace("&", "_x0026_")
                .Replace("/", "_x002F_");

            return input;
        }


        /// <summary>
        /// 根据数据列的列名取数据列的列索引
        /// </summary>
        /// <param name="dcc">数据列集合</param>
        /// <param name="columnName">数据列的列名</param>
        /// <returns></returns>
        public static int GetColumnIndexByColumnName(DataColumnCollection dcc, string columnName)
        {
            int result = -1;

            for (int i = 0; i < dcc.Count; i++)
            {
                if (dcc[i].ColumnName.ToLower() == columnName.ToLower())
                {
                    result = i;
                    break;
                }
            }

            return result;
        }
        #endregion

        #region Common Export Methods
        /// <summary>
        /// 导出
        /// <para>*  支持GridView导出Excel</para>
        /// </summary>
        public static void Export(GridView gdv, string fileName, string title, ExportFormat fileType)
        {
            DataTable dt = new DataTable();
            for (int i = 0; i < gdv.Columns.Count; i++)
            {
                if (gdv.Columns[i].HeaderText == "" || gdv.Columns[i].Visible == false)
                    continue;
                dt.Columns.Add(gdv.Columns[i].HeaderText);
            }

            for (int i = 0; i < gdv.Rows.Count; i++)
            {
                DataRow dr = dt.NewRow();
                for (int j = 0; j < gdv.Columns.Count; j++)
                {
                    if (gdv.Columns[j].HeaderText == "" || gdv.Columns[j].Visible == false)
                        continue;
                    dr[gdv.Columns[j].HeaderText] = gdv.Rows[i].Cells[j].Text;
                }
                dt.Rows.Add(dr);
            }
            Export(dt, fileName, title, fileType);
        }


        /// <summary>
        /// 导出
        /// <para>*  注意事项：DataTable的列头将转为Excel表格的列头,</para>
        /// </summary>
        /// <param name="dt">数据</param>
        /// <param name="fileName">文件名</param>
        /// <param name="title">标题,可为空</param>
        /// <param name="fileType">文件类型</param>
        public static void Export(DataTable dt, string fileName, string title, ExportFormat fileType)
        {
            Export(dt, fileName, null, title, fileType);
        }

        /// <summary>
        /// 导出
        /// <param name="dt">数据</param>
        /// <param name="fileName">文件名</param>
        /// <param name="heads">表头部分信息，表头的行数和字符串组的length一样。</param>
        /// <param name="title">标题,可为空</param>
        /// <param name="fileType">文件类型</param>
        /// </summary>
        public static void Export(DataTable dt, string fileName, string[] heads, string title, ExportFormat fileType)
        {
            //文件类型
            if (fileType == ExportFormat.Excel)
                ExportExcel(dt, fileName, heads, null, title);
            else
                ExportCSV(dt, fileName, title);
        }

        /// <summary>
        /// 导出
        /// <param name="dt">数据</param>
        /// <param name="fileName">文件名</param>
        /// <param name="heads">表头部分信息，表头的行数和字符串组的length一样。</param>
        /// <param name="columnNames">指定列名，列名数必须和DataTable的列名数一致，否则，不可用</param>
        /// <param name="title">标题,可为空</param>
        /// <param name="fileType">文件类型</param>
        /// </summary>
        public static void Export(DataTable dt, string fileName, string[] heads, string[] columnNames, string title, ExportFormat fileType)
        {
            //文件类型
            if (fileType == ExportFormat.Excel)
                ExportExcel(dt, fileName, heads, columnNames, title);
            else
            {
                ExportCSV(dt, columnNames, heads, fileName, title);
            }
        }
        #endregion

        /// <summary>
        /// 此方法为临时解决方案，解决导出CSV文件时，日期字符串在Excel显示为英文日期格式的问题
        /// <para>* 此方案为在现有的日期字符串前加空格</para>
        /// </summary>
        /// 创建人：
        /// 创建日期：2013-01-28
        /// <param name="dt">数据源</param>
        /// <param name="colNames">要控制的列。格式：,初登日期,记录日期,</param>
        /// <returns></returns>
        public static void DateConvert4CSV(DataTable dt, string colNames)
        {
            foreach (DataRow dr in dt.Rows)
            {
                foreach (DataColumn dc in dt.Columns)
                {
                    if (colNames.IndexOf(dc.ColumnName) > -1)
                        dr[dc] = " " + dr[dc];
                }
            }
        }

        #region 将Excel里面的数据生成DataSet，可以支持多sheet页请求

        /// <summary>
        /// Excel里面的数据生成DataSet，可以支持多sheet页请求
        /// </summary>
        /// <param name="filepath">文件的服务路径</param>
        /// <returns>返回Excel的数据集</returns>
        public static DataSet ImportDataTableByExcel(string filepath)
        {
            //根据后缀后来判断,当前运用Excel的版本.
            if (string.IsNullOrEmpty(filepath))
                return null;
            //取出后缀后
            string connstr =string.Empty;
            string extens=Path.GetExtension(filepath).ToUpper();
             if (".XLS".Equals(extens))//代表2003的版本Excel
             {
                 connstr = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + filepath + "';Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'");
             }
             if (".XLSX".Equals(extens))//代表2007版本的Excel
             {
                 connstr = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + filepath + "';Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'");
             }
            OleDbConnection conn = new OleDbConnection(connstr);


            ArrayList sheetNamelst=new ArrayList();

            DataSet dsExcel=new DataSet();

            #region 取出多个sheetName
            try
            {
                if(conn .State==ConnectionState.Closed)
                {
                    conn.Open();
                }
                //获取sheet页
                DataTable dtExcelSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
                                                                   new object[] {null, null, null, "Table"});
                for (int i = 0; i < dtExcelSchema.Rows.Count; i++)
                {
                    string sheetName = String.Format("{0}", dtExcelSchema.Rows[i]["TABLE_NAME"].ToString());
                    if (sheetName.Contains("$"))//过滤掉筛选系统产生的sheetName
                    {
                        string strend = sheetName.Substring(sheetName.IndexOf("$") + 1);
                        if (string.IsNullOrEmpty(strend)||strend.Equals("'"))//因为中文下将sheetName命名为加上单引号
                        {
                            sheetNamelst.Add(sheetName);
                        }

                    }
                }
            }
            catch (Exception)
            {
                //throw new Exception(ex.Message.ToString(),ex);
            }
            finally
            {
                conn.Close();
            }
            #endregion

            #region 取出sheet页里面的数据，并存放dsExcel里面
            try
            {
                string strSql = string.Empty;
                for (int i = 0; i < sheetNamelst.Count; i++)
                {
                    strSql = string.Format("select * from [{0}]", sheetNamelst[i].ToString());
                    OleDbDataAdapter da=new OleDbDataAdapter(strSql,conn);
                    DataTable dtExcel=new DataTable(sheetNamelst[i].ToString());
                    da.Fill(dtExcel);
                    dsExcel.Tables.Add(dtExcel);
                }
            }
            catch (Exception)
            {
                //throw new Exception(ex.Message.ToString(), ex);
            }
            #endregion

            return dsExcel;
        }

        #endregion

        #region 将Excel上传到服务器端,可以自定义上传文件的路径,并返回当上传的Excel的数据 

        /// <summary>
        /// 将Excel上传到服务器端,可以自定义上传文件的路径,并返回当上传的Excel的数据
        /// </summary>
        /// <param name="files">上传控件</param>
        /// <param name="ipath">上传路径</param>
        /// <returns>返回文件的数据集</returns>
        public static DataSet upfileExcel(HtmlInputFile files, string ipath)
        {
            try
            {
                string filename = "";//文件名
                string extension = "";//后缀名
                string returnstr = "";
                string name = files.PostedFile.FileName;
                if ("" != files.PostedFile.FileName)
                {
                    extension = Path.GetExtension(files.PostedFile.FileName).ToUpper();
                    if (!".XLS".Equals(extension) && (!".XLSX".Equals(extension)))
                        return null;
                    filename = Path.GetFileNameWithoutExtension(files.PostedFile.FileName);
                    if (String.IsNullOrEmpty(ipath))
                    {
                        ipath = "~/upfiles/procedureXls/";
                    }
                    string xmlpath = System.Web.HttpContext.Current.Server.MapPath(ipath);
                    if (!Directory.Exists(xmlpath))
                        Directory.CreateDirectory(xmlpath);
                    string realpath = xmlpath + filename + "_" + DateTime.Now.ToString("yyyy_mm_dd_hh_mm_ss") + extension;
                    files.PostedFile.SaveAs(realpath);
                    returnstr = realpath;
                    return ImportDataTableByExcel(returnstr);
                }
                return null;
            }
            catch (Exception)
            {

                return null;
            }
        }

        #endregion

        #region  直接将Excel文件上传到默认的路径,并返回当上传的Excel的数据 

        /// <summary>
        /// 直接将Excel文件上传到默认的路径,并返回当上传的Excel的数据
        /// </summary>
        /// <param name="files">上传控件</param>
        /// <returns>返回文件的数据集</returns>
        public static DataSet upfileExcel(HtmlInputFile files)
        {
            return upfileExcel(files, "");
        }

        #endregion
    }

    /// <summary>
    /// 导出文件的格式
    /// </summary>
    /// 创建人：
    /// 创建日期：
    public enum ExportFormat
    {
        /// <summary>
        /// CSV
        /// </summary>
        CSV,
        /// <summary>
        /// Excel
        /// </summary>
        Excel
    }
}